package com.pro01.backend.mapper;

import com.pro01.backend.dto.UserRatioSummaryDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface AdminRatioMapper {

    @Select("""
    SELECT
        u.id AS user_id,
        u.username,
        u.level,
        COALESCE(upload_stats.upload_total, 0) AS upload_total,
        COALESCE(download_stats.download_total, 0) AS download_total,
        CASE
            WHEN COALESCE(download_stats.download_total, 0) = 0 THEN
                CASE WHEN COALESCE(upload_stats.upload_total, 0) > 0 THEN 999.0 ELSE 0.0 END
            ELSE ROUND(upload_stats.upload_total / download_stats.download_total, 2)
        END AS share_ratio
    FROM users u
    LEFT JOIN (
        SELECT t.user_id, SUM(t.size * r.download_count) AS upload_total
        FROM torrents t
        LEFT JOIN (
            SELECT torrent_id, COUNT(*) AS download_count
            FROM tracker_reports
            WHERE event = 'COMPLETED'
            GROUP BY torrent_id
        ) r ON t.id = r.torrent_id
        GROUP BY t.user_id
    ) upload_stats ON u.id = upload_stats.user_id
    LEFT JOIN (
        SELECT user_id, SUM(downloaded) AS download_total
        FROM tracker_reports
        WHERE event = 'COMPLETED'
        GROUP BY user_id
    ) download_stats ON u.id = download_stats.user_id
    ORDER BY share_ratio DESC
    LIMIT #{pageSize} OFFSET #{offset}
""")
    List<UserRatioSummaryDTO> getUserRatios(@Param("offset") int offset, @Param("pageSize") int pageSize);

    @Select("SELECT COUNT(*) FROM users")
    int countAllUsers();

}
